package com.vma.offer.service;

import com.jfinal.core.Controller;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.IAtom;
import com.jfinal.plugin.activerecord.Page;
import com.jfinal.plugin.activerecord.Record;
import com.vma.shoes.bizModel.model.*;


import java.sql.SQLException;
import java.util.*;

/**
 * Created by chenzui on 2017/4/7.
 */
public class OfferService {
    public static void addOffer(Map map){
        final String offerName = ((String[]) map.get("offerName"))[0];
        final String type = ((String[]) map.get("type"))[0];
        final String brand = ((String[]) map.get("brand"))[0];
        final String inPrice = ((String[]) map.get("inPrice"))[0];
        final String outPrice = ((String[]) map.get("outPrice"))[0];
        final String saleCnt = ((String[]) map.get("saleCnt"))[0];
        final String content = ((String[]) map.get("content"))[0];
        String colorArr = ((String[]) map.get("colorArr"))[0];
        String lengthArr = ((String[]) map.get("lengthArr"))[0];
        String imgArr = ((String[]) map.get("imgUrls"))[0];
        final String[] colors =  colorArr.equals("") ? new String[0] : colorArr.split(",");
        final String[] lengths = lengthArr.equals("") ? new String[0] :lengthArr.split(",");
        final String[] imgs = imgArr.split(",");
        Db.tx(new IAtom() {
            public boolean run() throws SQLException {
                Offer offer = new Offer();
                offer.setOfferName(offerName).setBrand(brand).setBrandId(Integer.valueOf(brand)).setOStatus("1").setIfDelete("0")
                        .setOfferType(type).setInPrice(Float.valueOf(inPrice)).setStartSaleNum(Integer.valueOf(saleCnt)).setOutPrice(Float.valueOf(outPrice))
                        .setCreateTime(new Date()).save();
                if(colors != null && colors.length > 0){
                    Offer2Group offer2Group = new Offer2Group().setGroupId(1).setOfferId(offer.getId());
                    offer2Group.save();
                    for (String color:colors) {
                        new OfferGroup2Spec().setSpecId(Integer.valueOf(color)).setOfferGroupId(offer2Group.getId()).save();
                    }
                }
                if(lengths != null && lengths.length > 0){
                    Offer2Group offer2Group = new Offer2Group().setGroupId(1).setOfferId(offer.getId());
                    offer2Group.save();
                    for (String length:lengths) {
                        new OfferGroup2Spec().setSpecId(Integer.valueOf(length)).setOfferGroupId(offer2Group.getId()).save();
                    }
                }
                if(imgs != null && imgs.length > 0){
                    for (String img:imgs) {
                        OfferImg offerImg = new OfferImg();
                        offerImg.setCreateTime(new Date()).setOfferSpecId(offer.getId()).setUrl(img).save();
                    }
                }

                new OfferDetail().setOfferSpecId(offer.getId()).setContent(content).setCreateTime(new Date()).save();

                //新建商品销售记录表
                new OfferSale().setOfferId(offer.getId()).setCnt(0).setVersion(new Date()).save();
                return true;
            }
        });



    }

    public static void updateOffer(Map map){
        final String id = ((String[]) map.get("id"))[0];
        final String offerName = ((String[]) map.get("offerName"))[0];
        final String type = ((String[]) map.get("type"))[0];
        final String brand = ((String[]) map.get("brand"))[0];
        final String inPrice = ((String[]) map.get("inPrice"))[0];
        final String outPrice = ((String[]) map.get("outPrice"))[0];
        final String saleCnt = ((String[]) map.get("saleCnt"))[0];
        final String content = ((String[]) map.get("content"))[0];
        String colorArr = ((String[]) map.get("colorArr"))[0];
        String lengthArr = ((String[]) map.get("lengthArr"))[0];
        String imgArr = ((String[]) map.get("imgUrls"))[0];
        final String[] colors =  colorArr.equals("") ? new String[0] : colorArr.split(",");
        final String[] lengths = lengthArr.equals("") ? new String[0] :lengthArr.split(",");
        final String[] imgs = imgArr.split(",");
        Db.tx(new IAtom() {
            public boolean run() throws SQLException {
                Offer offer = Offer.dao.findById(id);
                offer.setOfferName(offerName).setBrand(brand).setBrandId(Integer.valueOf(brand)).setOStatus("1").setIfDelete("0")
                        .setOfferType(type).setInPrice(Float.valueOf(inPrice)).setStartSaleNum(Integer.valueOf(saleCnt)).setOutPrice(Float.valueOf(outPrice))
                        .setCreateTime(new Date()).update();

                List<Offer2Group> offer2Groups = Offer2Group.dao.find("SELECT id FROM offer_2_group WHERE offer_id = ? ",id);

                for(Offer2Group offer2Group:offer2Groups){
                    Db.update("delete FROM offer_group_2_spec WHERE offer_group_id=?",offer2Group.getId());
                }
                Db.update("delete FROM offer_2_group WHERE offer_id = ?",id);
//                Db.batch("offer_2_group",ids.toArray());
//                Db.deleteById("offer_group_2_spec","id",specIds.toArray());


                if(colors != null && colors.length > 0){
                    Offer2Group offer2Group = new Offer2Group().setGroupId(1).setOfferId(offer.getId());
                    offer2Group.save();
                    for (String color:colors) {
                        new OfferGroup2Spec().setSpecId(Integer.valueOf(color)).setOfferGroupId(offer2Group.getId()).save();
                    }
                }
                if(lengths != null && lengths.length > 0){

                    Offer2Group offer2Group = new Offer2Group().setGroupId(2).setOfferId(offer.getId());
                    offer2Group.save();
                    for (String length:lengths) {
                        new OfferGroup2Spec().setSpecId(Integer.valueOf(length)).setOfferGroupId(offer2Group.getId()).save();
                    }
                }
                if(imgs != null && imgs.length > 0){
                    for (String img:imgs) {
                        OfferImg offerImg = new OfferImg();
                        offerImg.setCreateTime(new Date()).setOfferSpecId(offer.getId()).setUrl(img).save();
                    }
                }

                new OfferDetail().setOfferSpecId(offer.getId()).setContent(content).setCreateTime(new Date()).save();


                return true;
            }
        });



    }


    public static Offer getOfferDetailForApp(Integer id,Integer userId){
        Offer offer = Offer.dao.findById(id);
        //查询图片
        List<OfferImg> imgs = OfferImg.dao.find("select * from offer_img where offer_spec_id = ?",id);
        //查询详情内容
        OfferDetail offerDetail = OfferDetail.dao.findFirst("SELECT * FROM offer_detail WHERE offer_spec_id = ? ORDER BY create_time DESC",id);
        //查询颜色
        List<SpecItem> offerColors = SpecItem.dao.find("SELECT si.id,si.spec_value FROM offer_2_group o2g,offer_group_2_spec o2g2s,spec_item si " +
                "WHERE o2g.id = o2g2s.offer_group_id and o2g.offer_id = ? " +
                "and si.id = o2g2s.spec_id and si.spec_group_id = 1",id);
        //查询长度
        List<SpecItem> offerLengths = SpecItem.dao.find("SELECT si.id,si.spec_value FROM offer_2_group o2g,offer_group_2_spec o2g2s,spec_item si " +
                "WHERE o2g.id = o2g2s.offer_group_id and o2g.offer_id = ? " +
                "and si.id = o2g2s.spec_id and si.spec_group_id = 2",id);

        offer.put("imgs",imgs);
        offer.put("detail",offerDetail);
        offer.put("colors",offerColors);
        offer.put("lengths",offerLengths);
        offer.put("brandDesc",Brand.dao.findById(offer.getBrandId()).getBrandName());
        offer.put("typeDesc",OfferType.dao.findById(offer.getOfferType()).getOfferTypeName());
        if (userId != 0){
            UserKeep userKeep = UserKeep.dao.findFirst("select * from user_keep where offer_id = ? and user_id = ? and status = 1",id,userId);
            if (userKeep != null){
                offer.put("keep","1");
            }else {
                offer.put("keep","0");
            }
        }
        //查询商品专场信息
        Offer2Market marketInfo = Offer2Market.dao.findFirst("SELECT * FROM offer_2_market WHERE offer_id = ? AND status = 1",id);
        if(marketInfo == null){
            offer.put("market_id",null);
        }else {
            offer.put("market_id",marketInfo.getMarketId());
        }
        OfferSale offerSale = OfferSale.dao.findFirst("SELECT * FROM offer_sale WHERE offer_id = ?",offer.getId());
        offer.put("saleCnt",offerSale.getCnt());
        return offer;

    }

    public static Map getOfferDetail(String id){
        Offer offer = Offer.dao.findById(id);

        List<OfferImg> imgs = OfferImg.dao.find("select * from offer_img where offer_spec_id = ?",id);

        offer.put("imgs",imgs);

        OfferDetail offerDetail = OfferDetail.dao.findFirst("SELECT * FROM offer_detail WHERE offer_spec_id = ? ORDER BY create_time DESC",id);

        offer.put("content",offerDetail);

        List<SpecItem> offerColors = SpecItem.dao.find("SELECT si.id,si.spec_value FROM offer_2_group o2g,offer_group_2_spec o2g2s,spec_item si " +
                "WHERE o2g.id = o2g2s.offer_group_id and o2g.offer_id = ? " +
                "and si.id = o2g2s.spec_id and si.spec_group_id = 1",id);

        List<SpecItem> offerLengths = SpecItem.dao.find("SELECT si.id,si.spec_value FROM offer_2_group o2g,offer_group_2_spec o2g2s,spec_item si " +
                "WHERE o2g.id = o2g2s.offer_group_id and o2g.offer_id = ? " +
                "and si.id = o2g2s.spec_id and si.spec_group_id = 2",id);

        offer.put("offerColors",offerColors);

        offer.put("offerLengths",offerLengths);

        Map initData = new HashMap();

        List<OfferType> types = OfferType.dao.find("SELECT offer_type_name,id FROM offer_type ORDER BY id");

        List<Brand> brands = Brand.dao.find("SELECT id,brand_name FROM brand");

        List<SpecItem> colors = SpecItem.dao.find("SELECT id,spec_value FROM spec_item WHERE spec_group_id = 1");

        List<SpecItem> lengths = SpecItem.dao.find("SELECT id,spec_value FROM spec_item WHERE spec_group_id = 2");

        initData.put("types",types);

        initData.put("brands",brands);

        initData.put("colors",colors);

        initData.put("lengths",lengths);

        initData.put("offer",offer);

        return initData;
    }

    public static Page<Record> qryOfferListByMarket(
            Integer userId,Integer cpage,Integer pSize,Integer marketId
            ,String type,String brandId,String action,String start,String end){
        List param = new ArrayList();
        String condition = "FROM offer_2_market o2m, offer_sale os, offer o LEFT JOIN user_keep uk ON o.id = uk.offer_id AND uk.status = 1 " ;

        condition+="AND uk.user_id = ? ";
        param.add(userId);

        condition += "WHERE o.id = o2m.offer_id AND o2m.market_id = ? AND os.offer_id = o.id  AND o.o_status =1 AND o2m.status=1 ";

        param.add(marketId);

        if(type != null && !"".equals(type)){
            param.add(type);
            condition+=" AND o.offer_type = ?";
        }
        if(brandId != null && !"".equals(brandId)){
            param.add(brandId);
            condition+=" AND o.brand_id = ?";
        }
        if(start != null && !"".equals(start)){
            param.add(start);
            condition+=" AND o.out_price >= ?";
        }
        if(end != null && !"".equals(end)){
            param.add(end);
            condition+=" AND o.out_price <= ?";
        }
        if(action!=null && !"".equals(action)){
            if(action.equals("1")){
                //价格升序
                condition += " ORDER BY o.out_price ASC";
            }else if(action.equals("2")) {
                //价格降序
                condition += " ORDER BY o.out_price DESC";
            }else if(action.equals("3")){
                //销量升序
                condition += " ORDER BY os.cnt ASC";
            }else if(action.equals("4")){
                //销量降序
                condition += " ORDER BY os.cnt DESC";
            }
        }
        Page<Record> offers = Db.paginate(cpage,pSize,"SELECT " +
                "  o.offer_name," +
                "  o.out_price," +
                "  o.id," +
                "  os.cnt," +
                "  IFNULL(uk.offer_id, 0) keep," +
                "  (SELECT oi.url" +
                "   FROM offer_img oi" +
                "   WHERE oi.offer_spec_id = o.id" +
                "   LIMIT 1)              url" ,
               condition,param.toArray());
        return offers;
    }

    public static void addKeep(Integer userId,Integer offerId){
        new UserKeep().setCreateTime(new Date()).setUserId(userId).setOfferId(offerId).setStatus("1").save();
    }

    public static void removeKeep(Integer userId,Integer offerId){
        UserKeep.dao.findFirst("SELECT * FROM user_keep WHERE offer_id = ? AND user_id = ? AND status = 1",offerId,userId).setStatus("0").update();
    }

    public static List<Offer> qryKeepOffersList(Integer userId){
        List<Offer> list = Offer.dao.find("SELECT\n" +
                "  o.offer_name,\n" +
                "  o.id,\n" +
                "  (SELECT url\n" +
                "   FROM offer_img oi\n" +
                "   WHERE oi.offer_spec_id = o.id\n" +
                "   LIMIT 1) url,\n" +
                "  (select count(*) FROM offer_2_market om WHERE om.offer_id = o.id and om.status ='1') flag,\n" +
                "  o.out_price,\n" +
                "  o.in_price\n" +
                "FROM user_keep uk, offer o\n" +
                "WHERE o.id = uk.offer_id AND uk.status = 1 AND uk.user_id = ?",userId);
        return list;
    }

    public static Page<Offer> qryOfferListNotInMarket(Integer cPage,Integer pSize,Integer userId){
        String sql = "SELECT * FROM offer o WHERE  id NOT IN (SELECT DISTINCT offer_id FROM offer_2_market WHERE status = 1)";
        Page<Offer> offerPage = Offer.dao.paginate(cPage,pSize,
                "SELECT o.id,o.in_price,o.out_price,o.offer_name,o.marked_id,sa.cnt,\n" +
                "  (SELECT url FROM offer_img oi WHERE oi.offer_spec_id = o.id LIMIT 1) url, ifnull(uk.user_id,0) keep ",
                "FROM offer o LEFT  JOIN user_keep uk ON o.id = uk.offer_id AND uk.status = 1 AND uk.user_id = ? " +
                        "LEFT JOIN offer_sale sa on sa.offer_id = o.id" +
                        " WHERE  o.id NOT IN (SELECT DISTINCT offer_id FROM offer_2_market WHERE status = 1)" +
                        " AND o.o_status = 1",userId);
        return offerPage;
    }


    public static Page<Offer> qryOfferListByKeyWords(Integer cPage,Integer pSize,String keyWord,Integer userId,String action){
        if(userId == null){
            userId = 0;
        }
        String condition="";
        if(action!=null && !"".equals(action)){
            if(action.equals("1")){
                //价格升序
                condition += " ORDER BY o.out_price ASC";
            }else if(action.equals("2")) {
                //价格降序
                condition += " ORDER BY o.out_price DESC";
            }else if(action.equals("3")){
                //销量升序
                condition += " ORDER BY sa.cnt ASC";
            }else if(action.equals("4")){
                //销量降序
                condition += " ORDER BY sa.cnt DESC";
            }
        }
        if(keyWord != null && !"".equals(keyWord)){
            List<Brand> brands = Brand.dao.find("SELECT * FROM brand WHERE brand_name LIKE ? AND brand.if_delete = 0","%"+keyWord+"%");
            if(brands != null && brands.size() != 0){
                String brandIds = "";
                for (Brand brand : brands){
                    brandIds += (brand.getId()+",");
                }
                brandIds = brandIds.substring(0,brandIds.length()-1);
                Page<Offer> offers = Offer.dao.paginate(cPage,pSize,"SELECT\n" +
                        "  o.id,\n" +
                        "  o.offer_name,\n" +
                        "  ifnull(uk.user_id,0) keep,\n" +
                        "  (SELECT url FROM offer_img oi WHERE o.id = oi.offer_spec_id LIMIT 1) url,\n" +
                        "  (select count(*) FROM offer_2_market om WHERE om.offer_id = o.id and om.status ='1') flag,\n" +
                        "  o.out_price,sa.cnt saleCnt,\n" +
                        "  o.in_price","FROM offer_sale sa,offer o LEFT JOIN user_keep uk ON o.id = uk.offer_id AND uk.user_id = ? AND uk.status = 1\n" +
                        "WHERE brand_id IN (?) AND sa.offer_id = o.id"+condition,userId,brandIds);
                return offers;
            }else {
                Page<Offer> offers = Offer.dao.paginate(cPage,pSize,"SELECT\n" +
                        "  o.id,\n" +
                        "  o.offer_name,\n" +
                        "  ifnull(uk.user_id, 0)                          keep,\n" +
                        "  (SELECT url\n" +
                        "   FROM offer_img oi\n" +
                        "   WHERE o.id = oi.offer_spec_id\n" +
                        "   LIMIT 1)                                      url,\n" +
                        "  (SELECT count(*)\n" +
                        "   FROM offer_2_market om\n" +
                        "   WHERE om.offer_id = o.id AND om.status = '1') flag,\n" +
                        "  o.out_price,\n" +
                        "  sa.cnt saleCnt,\n" +
                        "  o.in_price","FROM offer_sale sa, offer o LEFT JOIN user_keep uk ON o.id = uk.offer_id AND uk.user_id = ? AND uk.status = 1\n" +
                        "WHERE sa.offer_id = o.id AND offer_name LIKE ?"+condition,userId,"%"+keyWord+"%");
                return offers;
            }
        }else{
            return null;
        }
    }


    public static List<Brand> qryBrandList(){
        List<Brand> brands = Brand.dao.find("select * from brand where if_delete = 0");
        return brands;
    }

    public static List<OfferType> qryTypeList(){
        List<OfferType> types = OfferType.dao.find("SELECT * FROM offer_type where if_delete = 0");
        return types;
    }
}
